﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DAL
/// </summary>
public sealed class DAL
{
    public DAL()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    #region "COMMON"
    private static string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["Kontinum"].ConnectionString;
    }
    public static bool InsertError(string ErrorGUID, string ErrorMessage, string ErrorFunction)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = @"spInsertApplicationErrors";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ErrorGUID", SqlDbType.NVarChar, 50)).Value = ErrorGUID;
                cmd.Parameters.Add(new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, -1)).Value = ErrorMessage;
                cmd.Parameters.Add(new SqlParameter("@ErrorFunction", SqlDbType.NVarChar, 50)).Value = ErrorFunction;

                cnn.Open();
                cmd.ExecuteScalar();
                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex);
            return false;
        }
    }
    public static DataTable GetUser(string LoginName, string LoginPassword)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@LoginName", SqlDbType.NVarChar,50);
        p.Value = LoginName;
        spParamList.Add(p);
        p = new SqlParameter("@LoginPassword", SqlDbType.NVarChar, 50);
        p.Value = LoginPassword;
        spParamList.Add(p);
        return ExecSQLS("spSelectApplicationUserLogin", spParamList);
    }
    public static DataTable GetUserRole(int UserId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@UserId", SqlDbType.Int);
        p.Value = UserId;
        spParamList.Add(p);
        return ExecSQLS("spSelectApplicationRoleByUserId", spParamList);
    }
    public static DataTable GetRolePermission(int RoleId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@RoleId", SqlDbType.Int);
        p.Value = RoleId;
        spParamList.Add(p);
        return ExecSQLS("spSelectApplicationPermissionByRoleId", spParamList);
    }
    #endregion

    #region "Customer"
    public static DataTable GetCustomerById(int CustomerId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@CustomerId", SqlDbType.BigInt);
        p.Value = CustomerId;
        spParamList.Add(p);
        return ExecSQLS("spSelectCustomerById", spParamList);
    }
    public static DataTable GetCustomerList(bool IsActive = true)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@IsActive", SqlDbType.Bit);
        p.Value = IsActive;
        spParamList.Add(p);

        return ExecSQLS("spSelectCustomerList", spParamList);
    }
    public static DataTable GetCustomerListByName(string TextToSearch)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@TextToSearch", SqlDbType.NVarChar, 50);
        p.Value = TextToSearch;
        spParamList.Add(p);
        return ExecSQLS("spSelectCustomersByName", spParamList);

    }
    public static bool InsertCustomer(string CustomerCode, string CustomerName, string RegisteredBy, out int RecordId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@CustomerCode", SqlDbType.NVarChar, 3);
        p.Value = CustomerCode;
        spParamList.Add(p);
        p = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
        p.Value = CustomerName;
        spParamList.Add(p);
        p = new SqlParameter("@RegisteredBy", SqlDbType.NVarChar, 50);
        p.Value = string.IsNullOrEmpty(RegisteredBy) ? (object)DBNull.Value : (object)RegisteredBy;
        spParamList.Add(p);

        return ExecSQLI("spInsertCustomer",spParamList,out RecordId);

    }
    public static bool UpdateCustomer(int CustomerId, string CustomerName, string UnRegisteredBy)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@CustomerId", SqlDbType.BigInt);
        p.Value = CustomerId;
        spParamList.Add(p);
        p=new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
        p.Value = CustomerName;
        spParamList.Add(p);
        p=new SqlParameter("@UnRegisteredBy", SqlDbType.NVarChar, 50);
        p.Value = string.IsNullOrEmpty(UnRegisteredBy) ? (object)DBNull.Value : (object)UnRegisteredBy;
        spParamList.Add(p);

        return ExecSQLUD("spUpdateCustomer", spParamList);
    }
    public static bool CustomerCodeValidate(string CustomerCode)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = "spSelectCustomerCodeValidate";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@CustomerCode", SqlDbType.NVarChar, 3)).Value = CustomerCode;
                cnn.Open();
                int existcount = Int32.Parse(cmd.ExecuteScalar().ToString());

                if (existcount>0)
                {
                    return false;
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, "CustCodeValidate");
            return false;
        }
    }
    #endregion

    #region "Client"
    public static DataTable GetClientById(int ClientId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@ClientId", SqlDbType.BigInt);
        p.Value = ClientId;
        spParamList.Add(p);
        return ExecSQLS("spSelectClientById", spParamList);

    }
    public static DataTable GetClientList(int CustomerId, bool IsActive = true)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@CustomerId", SqlDbType.BigInt);
        p.Value = CustomerId;
        spParamList.Add(p);
        p = new SqlParameter("@IsActive", SqlDbType.Bit);
        p.Value = IsActive;
        spParamList.Add(p);
        
        return ExecSQLS("spSelectClientList", spParamList);        

    }
    public static DataTable GetClientListByName(string TextToSearch)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@TextToSearch", SqlDbType.NVarChar,50);
        p.Value = TextToSearch;
        spParamList.Add(p);
        return ExecSQLS("spSelectClientsByName", spParamList);

    }
    public static bool InsertClient(string ClientCode, string ClientName, int CustomerId, string RegisteredBy, out int RecordId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@ClientCode", SqlDbType.NVarChar, 3);
        p.Value = ClientCode;
        spParamList.Add(p);
        p = new SqlParameter("@ClientName", SqlDbType.NVarChar, 50);
        p.Value = ClientName;
        spParamList.Add(p);
        p = new SqlParameter("@CustomerId", SqlDbType.BigInt);
        p.Value = CustomerId;
        spParamList.Add(p);
        p = new SqlParameter("@RegisteredBy", SqlDbType.NVarChar, 50);
        p.Value = RegisteredBy;
        spParamList.Add(p);

        return ExecSQLI("spInsertClient", spParamList, out RecordId);
        
    }
    public static bool UpdateClient(int ClientId, string ClientName, string UnRegisteredBy)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@ClientId", SqlDbType.BigInt);
        p.Value = ClientId;
        spParamList.Add(p);
        p = new SqlParameter("@ClientName", SqlDbType.NVarChar, 50);
        p.Value = ClientName;
        spParamList.Add(p);
        p = new SqlParameter("@UnRegisteredBy", SqlDbType.NVarChar, 50);
        p.Value = string.IsNullOrEmpty(UnRegisteredBy) ? (object)DBNull.Value : (object)UnRegisteredBy; ;
        spParamList.Add(p);
        return ExecSQLUD("spUpdateClient", spParamList);
    }
    public static bool ClientCodeValidate(string ClientCode)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = "spSelectClientCodeValidate";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ClientCode", SqlDbType.NVarChar, 3)).Value = ClientCode;
                cnn.Open();
                int existcount = Int32.Parse(cmd.ExecuteScalar().ToString());

                if (existcount > 0)
                {
                    return false;
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, "CustCodeValidate");
            return false;
        }
    }
    #endregion

    #region "LOV"
    public static DataTable GetLOVList(ENU.Entity CustomerOrClient, int CustomerOrClientId, string RecordType, string RecordName)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@CustomerOrClientId", SqlDbType.BigInt);
        p.Value = CustomerOrClientId;
        spParamList.Add(p);
        p = new SqlParameter("@CustomerOrClient", SqlDbType.NVarChar, 8);
        p.Value = CustomerOrClient.ToString();
        spParamList.Add(p);
        p=new SqlParameter("@RecordType", SqlDbType.NVarChar, 50);
        p.Value = string.IsNullOrEmpty(RecordType) ? (object)DBNull.Value : (object)RecordType;
        spParamList.Add(p);
        p=new SqlParameter("@RecordName", SqlDbType.NVarChar, 50);
        p.Value = string.IsNullOrEmpty(RecordName) ? (object)DBNull.Value : (object)RecordName;

        return ExecSQLS("spSelectLOV", spParamList);

    }
    public static bool InsertLOV(int CustomerOrClientId, ENU.Entity CustomerOrClient, ENU.CCLoVRecordType RecordType, ENU.CCLoVRecordName RecordName, string Description, string RecordValue, string LastSavedBy, out int RecordId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@CustomerOrClientId", SqlDbType.BigInt);
        p.Value = CustomerOrClientId;
        spParamList.Add(p);
        p = new SqlParameter("@CustomerOrClient", SqlDbType.NVarChar, 8);
        p.Value = CustomerOrClient.ToString();
        spParamList.Add(p);
        p = new SqlParameter("@RecordType", SqlDbType.NVarChar, 50);
        p.Value = RecordType.ToString();
        spParamList.Add(p);
        p = new SqlParameter("@RecordName", SqlDbType.NVarChar, 50);
        p.Value = RecordName.ToString();
        spParamList.Add(p);
        p = new SqlParameter("@Description", SqlDbType.NVarChar, 50);
        p.Value = Description;
        spParamList.Add(p);
        p = new SqlParameter("@RecordValue", SqlDbType.NVarChar, 500);
        p.Value = RecordValue;
        spParamList.Add(p);
        p = new SqlParameter("@LastSavedBy", SqlDbType.NVarChar, 50);
        p.Value = LastSavedBy;
        spParamList.Add(p);
        return ExecSQLI("spInsertLOV", spParamList, out RecordId);
    }
    public static bool UpdateLOV(int RecId, string Description, string RecordValue, string LastSavedBy)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@RecId", SqlDbType.BigInt);
        p.Value = RecId;
        spParamList.Add(p);
        p = new SqlParameter("@Description", SqlDbType.NVarChar, 50);
        p.Value = Description;
        spParamList.Add(p);
        p = new SqlParameter("@RecordValue", SqlDbType.NVarChar, 500);
        p.Value = RecordValue;
        spParamList.Add(p);
        p = new SqlParameter("@LastSavedBy", SqlDbType.NVarChar, 50);
        p.Value = LastSavedBy;
        spParamList.Add(p);
        return ExecSQLUD("spUpdateLOV", spParamList);
    }
    public static bool DeleteLOV(int RecId)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@RecId", SqlDbType.BigInt);
        p.Value = RecId;
        spParamList.Add(p);
        return ExecSQLUD("spDeleteLOV", spParamList);
    }
    #endregion

    #region "SQLOperations"

    public static bool ExecSQLUD(string spname, List<SqlParameter> spparams)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = spname;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(spparams.ToArray());
                cnn.Open();
                cmd.ExecuteScalar();
                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, spname);
            return false;
        }
    }
    public static bool ExecSQLI(string spname, List<SqlParameter> spparams, out int RecordId)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = spname;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(spparams.ToArray());
                cnn.Open();
                RecordId = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, spname);
            RecordId = -1;
            return false;
        }
    }
    public static DataTable ExecSQLS(string spname, List<SqlParameter> spparams)
    {
        DataTable t = new DataTable();
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = spname;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(spparams.ToArray());
                cnn.Open();
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    adp.Fill(t);
                }
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex);
        }

        return t;
    }
    #endregion

    #region "Workflow"
    public static DataTable GetProjectListByClientId(int ClientId,ENU.WFCode WFCode ,ENU.WFICompletionStatus CompletionStatus)
    {
        List<SqlParameter> spParamList = new List<SqlParameter>();
        SqlParameter p = new SqlParameter("@ClientId", SqlDbType.BigInt);
        p.Value = ClientId;
        spParamList.Add(p);
        p = new SqlParameter("@CompletionStatus", SqlDbType.TinyInt);
        p.Value = (int)CompletionStatus;
        spParamList.Add(p);
        p = new SqlParameter("@WorkflowCode", SqlDbType.NVarChar, 2);
        p.Value = WFCode;
        spParamList.Add(p);
        return ExecSQLS("spSelectProjectCodes", spParamList);
    }
    public static bool WFStart(ENU.WFCode WFC,int ClientId, int CustomerId, out string ProjectCode)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = "spWorkflowStart";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@WorkflowCode", SqlDbType.NVarChar, 50)).Value = WFC.ToString();
                cmd.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int)).Value = ClientId;
                cmd.Parameters.Add(new SqlParameter("@CustomerId", SqlDbType.Int)).Value = CustomerId;
                cnn.Open();
                ProjectCode = cmd.ExecuteScalar().ToString();
                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, "RPStart");
            ProjectCode = null;
            return false;
        }
    }
    public static bool WFProceed(string ProjectCode, out int RecId, out string ErrorCode)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = "spWorkflowProceed";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ProjectCode", SqlDbType.NVarChar, 50)).Value = ENU.WFCode.RP.ToString();
                cnn.Open();
                RecId = Int32.Parse(cmd.ExecuteScalar().ToString());
                //************************************************
                ErrorCode = "";
                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, "RPStart");
            RecId = -1;
            //************************************************
            ErrorCode = "";
            return false;
        }
    }
    public static bool WFRollBack(string ProjectCode, out int RecId, out string ErrorCode)
    {
        try
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = cnn.CreateCommand();

                cmd.CommandText = "spWorkflowRollback";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ProjectCode", SqlDbType.NVarChar, 50)).Value = ENU.WFCode.RP.ToString();
                cnn.Open();
                RecId = Int32.Parse(cmd.ExecuteScalar().ToString());
                //************************************************
                ErrorCode = "";
                return true;
            }
        }
        catch (Exception ex)
        {
            ERR.Handler(ex, "RPStart");
            RecId = -1;
            //************************************************
            ErrorCode = "";
            return false;
        }
    }

    #endregion

}